package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.FamilyArchives;
import com.groot.medical.entity.Pay;

public class PayDaoImpl implements PayDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllPayCount(String currentYear, String townCode, String holdername) {
		int count = 0;
        //sql拼接
		
		StringBuffer sql = new StringBuffer("SELECT COUNT(id) AS id FROM t_pay where 1=1");
	
        if(currentYear != null && !currentYear.trim().equals("")) {
            sql.append(" AND current_year like '%"+currentYear+"%'");
        }if(townCode != null && !townCode.trim().equals("")){
        	sql.append(" AND family_code like '%"+townCode+"%'");
        }if(holdername != null && !holdername.trim().equals("")){
        	sql.append(" AND name like '%"+holdername+"%'");
        }
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;

	}

	@Override
	public List<Pay> findFamilyByPage(int startIndex, int pageSize, String currentYear, String townCode,
			String holdername) {
		StringBuffer sql = new StringBuffer("select * from t_pay where 1=1");
		
		if(currentYear != null && !currentYear.trim().equals("")) {
            sql.append(" AND current_year like '%"+currentYear+"%'");
        }if(townCode != null && !townCode.trim().equals("")){
        	sql.append(" AND family_code like '%"+townCode+"%'");
        }if(holdername != null && !holdername.trim().equals("")){
        	sql.append(" AND name like '%"+holdername+"%'");
        }
        sql.append(" order by pay_time desc limit ?,? " );
        System.out.println(sql.toString());
        try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<Pay> pays = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				Pay pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setFarmingCardCord(rs.getString(2));
				pay.setFamilyCode(rs.getString(3));
				pay.setIdcardCode(rs.getString(4));
				pay.setName(rs.getString(5));
				pay.setPayMoney(rs.getString(6));
				pay.setPayTime(rs.getDate(7));
				pay.setCurretYear(rs.getString(8));
				pay.setOperater(rs.getString(9));
				pay.setVoucherId(rs.getString(10));
				pays.add(pay);
				
			}
			
			return pays;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

	@Override
	public List<Pay> findPayInfoByFamilyCodeAndYear(String familyCode, String nowTime) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_pay where family_code = ? AND current_year = ? ";
		
		try {
			ps = conn.prepareStatement(sql);
		    ps.setString(1, familyCode);
		    ps.setString(2, nowTime);
			rs = ps.executeQuery();
			 
			List<Pay> payList = new ArrayList<>();
			
			while(rs.next()){
				Pay pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setFarmingCardCord(rs.getString(2));
				pay.setFamilyCode(rs.getString(3));
				pay.setIdcardCode(rs.getString(4));
				pay.setName(rs.getString(5));
				pay.setPayMoney(rs.getString(6));
				pay.setPayTime(rs.getDate(7));
				pay.setCurretYear(rs.getString(8));
				pay.setOperater(rs.getString(9));
				pay.setVoucherId(rs.getString(10));
				payList.add(pay);
				
			}
			
			return payList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	
	private java.sql.Date fomart(java.util.Date data){
		return new java.sql.Date(data.getTime());
	}

	@Override
	public void payMoneyForPerson(List<Pay> payPerson) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			
			
			for(int i=0;i<payPerson.size();i++)
			{
			String sql="insert into t_pay(farmingcard_code,family_code,idcard_code,name,pay_money,pay_time,current_year,operater,voucher_id)values(?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, payPerson.get(i).getFarmingCardCord());
			ps.setString(2, payPerson.get(i).getFamilyCode());
			ps.setString(3, payPerson.get(i).getIdcardCode());
			ps.setString(4, payPerson.get(i).getName());
			ps.setString(5, payPerson.get(i).getPayMoney());
			
			ps.setDate(6, fomart(payPerson.get(i).getPayTime()));
			ps.setString(7, payPerson.get(i).getCurretYear());
			ps.setString(8, payPerson.get(i).getOperater());
			ps.setString(9, payPerson.get(i).getVoucherId());
			
			ps.executeUpdate();
			}
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public List<Pay> findAllPayInfo() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_pay";
		
		try {
			ps = conn.prepareStatement(sql);
		  
			rs = ps.executeQuery();
			 
			List<Pay> payList = new ArrayList<>();
			
			while(rs.next()){
				Pay pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setFarmingCardCord(rs.getString(2));
				pay.setFamilyCode(rs.getString(3));
				pay.setIdcardCode(rs.getString(4));
				pay.setName(rs.getString(5));
				pay.setPayMoney(rs.getString(6));
				pay.setPayTime(rs.getDate(7));
				pay.setCurretYear(rs.getString(8));
				pay.setOperater(rs.getString(9));
				pay.setVoucherId(rs.getString(10));
				payList.add(pay);
			}
			
			return payList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}

	@Override
	public List<Pay> findPayInfoByCondition(String currentYear, String townCode, String holdername) {
StringBuffer sql = new StringBuffer("select * from t_pay where 1=1");
		
		if(currentYear != null && !currentYear.trim().equals("")) {
            sql.append(" AND current_year like '%"+currentYear+"%'");
        }if(townCode != null && !townCode.trim().equals("")){
        	sql.append(" AND family_code like '%"+townCode+"%'");
        }if(holdername != null && !holdername.trim().equals("")){
        	sql.append(" AND name like '%"+holdername+"%'");
        }
        sql.append(" order by pay_time desc " );
        System.out.println(sql.toString());
        try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
		
			rs = ps.executeQuery();
			List<Pay> pays = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				Pay pay = new Pay();
				pay.setId(rs.getInt(1));
				pay.setFarmingCardCord(rs.getString(2));
				pay.setFamilyCode(rs.getString(3));
				pay.setIdcardCode(rs.getString(4));
				pay.setName(rs.getString(5));
				pay.setPayMoney(rs.getString(6));
				pay.setPayTime(rs.getDate(7));
				pay.setCurretYear(rs.getString(8));
				pay.setOperater(rs.getString(9));
				pay.setVoucherId(rs.getString(10));
				pays.add(pay);
				
			}
			
			return pays;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

	@Override
	public boolean findPersonInfoByIdAndYear(String idCardNum, String nowTime) {
		boolean flag=false;
		int count=0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(1) AS id FROM t_pay where idcard_code=? AND current_year=?");
		//查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, idCardNum);
 		     ps.setString(2, nowTime);
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
            if(count==1){
            	flag=true;
            }
            
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
		return flag;
	}

	@Override
	public int findIsExsitService(String familyCode) {
		//查询结果
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(family_code) id FROM t_pay WHERE family_code=?");
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, familyCode);
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;
	}

	@Override
	public int findIsExsitPayService(String idCardNum) {
		//查询结果
				int count = 0;
				StringBuffer sql = new StringBuffer("SELECT COUNT(idcard_code) id FROM t_pay WHERE idcard_code=?");
		        try {
		        	 conn = MedicalDBUtil.getConnection();
		             ps = conn.prepareStatement(sql.toString());
		             ps.setString(1, idCardNum);
		             rs = ps.executeQuery();
		            if(rs.next()) {
		                count = rs.getInt("id");
		            }
		        } catch (SQLException e) {

		    		e.printStackTrace();
		    		}
		    		finally{
		    			MedicalDBUtil.closeAll(conn, ps, rs);
		    		}
		        return count;
	}

}
